﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EmotionRecognitionEntities;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.IO;
using System.Data;

namespace EmotionRecognitionDB
{//
    


    public class DBQueries
    {

        static private DBConnection db = null;


        public DBQueries()
        {
           
        }

        #region Pictures Dictionaries

        /// <summary>
        /// InitialPicturesDictionary- will be used for holding Initial endwoment Pictures 
        /// </summary>
        static Dictionary<int, Picture> InitialPicturesDictionary = new Dictionary<int, Picture>();

        /// reviewPictuersDictionary- will be used for holding Forreview Pictuers 
        static Dictionary<int, Picture> reviewPictuersDictionary = new Dictionary<int, Picture>();

        /// newAddedPictuersDictionary- will be used for holding newAddedPictuers Pictuers - for expert Review
        static Dictionary<int, Picture> newAddedPictuersDictionary = new Dictionary<int, Picture>();

        #endregion

        #region EnvironmentVariables

       // static  string initalPictures = Environment.GetEnvironmentVariable("InitialPictures");
        static  string databasePictures = Environment.GetEnvironmentVariable("DataBasePictures");
        static  string externalPictures = Environment.GetEnvironmentVariable("ExternalPictures");
        //set index vars
        static  Dictionary<int, int> chunkSizes = null;
        static  List<Chunk> chunks = null;

        static Dictionary<int, string> pathMatcher = new Dictionary<int, string>();
        #endregion
        public static ReplyMsg returnSysVarPath(int sysVar)
        {
            return new ReplyMsg(true, pathMatcher[sysVar]);
        }
        
        #region LoginMethods

        public static ReplyMsg LoginRequest(UserProperties user)
        {
            try
            {
               
                db = DBConnection.Instance;
                
                db.command.CommandText = ("SELECT user.iduser, user.Role FROM user WHERE user.Name = @name and user.Password = @password;");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@Name", user.name);
                db.command.Parameters.AddWithValue("@Password", user.password);
                db.reader = db.command.ExecuteReader();
            
                if (db.reader.Read())
                {
                    if (db.reader.GetInt32(0) != 0)
                    {
                        //Get userid from fisrt Column , get role from Second column
                        ReplyMsg reply = new ReplyMsg(true, db.reader.GetInt32(0), db.reader.GetBoolean(1));
                        //Need to close reader before Exit the method
                        db.command.Parameters.Clear();
                        db.reader.Close();
                        return reply;

                    }
                }
                db.reader.Close();
                return new ReplyMsg(false);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }

        }

        #endregion

        #region RegisterationRequest method
        public static ReplyMsg RegisterationRequest(UserProperties user)
        {
            try
            {
                db = DBConnection.Instance;
                db.command.CommandText = ("INSERT INTO `finalproject`.`user` (`Name`, `Role`, `Password`) VALUES (@Name, @Role, @Password);");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@Name", user.name);
                db.command.Parameters.AddWithValue("@Role", user.role);
                db.command.Parameters.AddWithValue("@Password", user.password);
                db.command.ExecuteNonQuery();
                return new ReplyMsg(true);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return new ReplyMsg(false);
            }
        }

        #endregion

        #region InitalEndowment methods

        /// <summary>
        /// Get ChunksLabels- method to get chunksLabels
        /// 
        /// </summary>
        /// <returns></returns>
        public static ReplyMsg GetChunks()
        {

            try
            {
                List<Chunk> listlables = new List<Chunk>();
                db = DBConnection.Instance;
                db.command.CommandText = ("SELECT * FROM chunk order by idChunk;");
                db.reader = db.command.ExecuteReader();

                while (db.reader.Read())
                {
                    Chunk chunk = new Chunk(db.reader.GetInt32(0), db.reader.GetString(1));
                    listlables.Add(chunk);
                }

                db.reader.Close();
                return new ReplyMsg(true, listlables);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }

        }

        /// <summary>
        /// LoadPicutersFromDB method will be used by AUDectector
        /// 1.will load pictures and add them to dictionary
        /// 2.Au detector will send them to Visage 
        /// 3.each Pictures will be initialized with AU`s
        /// 4.loadingType- 0- initialEndowmentPictures
        ///                1-ForReviewPictures
        ///                2- Recently new Added pictures
        /// </summary>
        public static ReplyMsg LoadPicutersFromDB(int LoadingType)
        {
            try
            {
                db = DBConnection.Instance;
                //Get All AU`s
                ReplyMsg reply = GetAllAus();
                //will be used to check if we need AU from xml or not
                List<int> currentAUsInDB = (List<int>)reply.ParamArray[0];
              switch(LoadingType)
              {
                  //initialEndowment loading 
                  case 0:
                      {

                      InitialPicturesDictionary.Clear();

                      db.command.CommandText = ("SELECT * FROM pictures where ForReview=0;");
                      
                      db.reader = db.command.ExecuteReader();

                      while (db.reader.Read())
                      {
                          //add pictures to dictionary
                          //Create Picture(pictureid,Path,chunkId,consistencyRate
                          //1. from database we get picture id && Path
                          //2. new picture will be set with dummy chunkId-0 && Ischeked-0

                          //path for initialendwoment pictures will - path of  Environment variable initalPictures + name of file(from DB)
                         
                          //if picture dont exists - Create new one
                          if (!InitialPicturesDictionary.ContainsKey(db.reader.GetInt32(0)))
                          {
                              //Create picture
                              Picture picture = new Picture(db.reader.GetInt32(0), pathMatcher[db.reader.GetInt32(6)] + db.reader.GetString(1), db.reader.GetInt32(2), db.reader.GetInt32(3));
                              //add to InitialPicturesDictionary

                              if(picture.IsChecked!=2)
                              InitialPicturesDictionary.Add(picture.PictureID, picture);

                          }
                      }

                      db.reader.Close();
                      //Get Au`s of pictures from DB
                      GetAUsOfPicturesFromDB(InitialPicturesDictionary);

                      //Go over all pictures - if Aulist is empty
                       //1.Read Au`s from XML
                       //2.AddAU`s pictures to DataBase
                      foreach (Picture pic in InitialPicturesDictionary.Values)
                      {
                          if (pic.AUList.Count == 0)
                          {
                              pic.LoadAUsFromXML(currentAUsInDB);
                              AddAusToPictureInDB(pic);
                          }
                      }
                   
                      
                      return new ReplyMsg(true, InitialPicturesDictionary);

                  }
               //Database pictures loading
              case 1:
                  {

                      if (reviewPictuersDictionary.Count != 0)
                          return new ReplyMsg(true, reviewPictuersDictionary);

                      db.command.CommandText = ("SELECT * FROM pictures where ForReview=1 and HasAUs=1;");
                      
                      db.reader = db.command.ExecuteReader();
                      while (db.reader.Read())
                      {

                          if (!reviewPictuersDictionary.ContainsKey(db.reader.GetInt32(0)))
                          {
                              Picture picture = new Picture(db.reader.GetInt32(0), pathMatcher[db.reader.GetInt32(6)] + db.reader.GetString(1), db.reader.GetInt32(2), db.reader.GetInt32(3));
                              reviewPictuersDictionary.Add(picture.PictureID, picture);
                          }
                      }

                      db.reader.Close();
                      
                      //Get Au`s of pictures from DB
                      GetAUsOfPicturesFromDB(reviewPictuersDictionary);


                      //Go over all pictures - if Aulist is empty
                      //1.Read Au`s from XML
                      //2.AddAU`s pictures to DataBase
                      foreach (Picture pic in reviewPictuersDictionary.Values)
                      {
                          if (pic.AUList.Count == 0)
                          {
                              pic.LoadAUsFromXML(currentAUsInDB);
                              AddAusToPictureInDB(pic);
                          }
                      }

                      
                      return new ReplyMsg(true, reviewPictuersDictionary);
                  }
              
              //new added pictures loading
              case 2:
                  {

                      db.command.CommandText = ("SELECT * FROM pictures where ForReview=1 and HasAUs=0;");
                      
                      db.reader = db.command.ExecuteReader();
                      while (db.reader.Read())
                      {

                          Picture picture = new Picture(db.reader.GetInt32(0),pathMatcher[db.reader.GetInt32(6)] + db.reader.GetString(1), db.reader.GetInt32(2), db.reader.GetInt32(3));
                          //Get AU`s of pictures from DataBase
                         
                          //add pictures to Dictionary 
                          //<int,picture>-  <pictureid,picture>
                          if (!newAddedPictuersDictionary.ContainsKey(db.reader.GetInt32(0)))
                              newAddedPictuersDictionary.Add(picture.PictureID, picture);

                      }

                      db.reader.Close();
                      return new ReplyMsg(true, newAddedPictuersDictionary);
                  }
                }
                return new ReplyMsg(false);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }


        }
      
        /// <summary>
        /// Accept Emotion method 
        /// 1. will call Create new case
        /// will be called by Gui
        /// </summary>
        /// <param name="chunkId"></param>
        /// <param name="pictureId"></param>
        /// <returns></returns>
        public static ReplyMsg AcceptEmotion(int chunkId, int pictureID, int userID)
        {
            return CreateNewCase(pictureID, chunkId, false, userID);
        }

        /// <summary>
        /// method to create a new case in Database
        /// 1. table case will be updated with new entry
        /// 2.will be called by Gui
        /// </summary>
        /// <param name="pictureID"></param>
        /// <param name="description"></param>
        /// <param name="chunkid"></param>
        /// <param name="index"></param>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static ReplyMsg CreateNewCase(int idCase, int Typicality, bool IsIndex, int chunk_idChunk, int User_idUser, List<int> auList)
        {
            try
            {

                db = DBConnection.Instance;
                db.command.CommandText = ("INSERT INTO `finalproject`.`case` (`idCase`,`Typicality`, `chunk_idChunk`, `IsIndex`, `User_idUser`) VALUES (@idCase,@Typicality, @chunk_idChunk, @IsIndex, @User_idUser);");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@idCase", idCase);
                db.command.Parameters.AddWithValue("@Typicality", Typicality);
                db.command.Parameters.AddWithValue("@chunk_idChunk", chunk_idChunk);
                db.command.Parameters.AddWithValue("@IsIndex", IsIndex);
                db.command.Parameters.AddWithValue("@User_idUser", User_idUser);
                db.command.ExecuteNonQuery();
                db.command.Parameters.Clear();
                if (AddAuListToCaseInDB(idCase,auList) == true) 
                    return new ReplyMsg(true );
                else
                    return new ReplyMsg(true);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return new ReplyMsg(false);
            }



        }
        public static bool AddAuListToCaseInDB(int caseID, List<int> auList)
        {
            
            try
            {
                db = DBConnection.Instance;

                //Go over all Au`s of picture and add it to database
                foreach (int au in auList)
                {
                    db.command.CommandText = ("INSERT INTO `finalproject`.`case_has_au` (`au_idAU`, `case_IdCase`) VALUES (@au_idAU, @case_IdCase);");
                    db.command.Prepare();
                    db.command.Parameters.Clear();
                    db.command.Parameters.AddWithValue("@au_idAU", au);
                    db.command.Parameters.AddWithValue("@case_IdCase", caseID);
                    db.command.ExecuteNonQuery();
                    db.command.Parameters.Clear();
                }
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return false;
            }


        }
        public static ReplyMsg CreateNewCase(int pictureID, int chunkid, bool index, int userID)
        {
            int lastUsedCaseId = 0;
            try
            {

                db = DBConnection.Instance;
                db.command.CommandText = ("SELECT case.idCase FROM finalproject.case ORDER BY case.idCase");


                db.reader = db.command.ExecuteReader();
                //go to the Last line
                if (db.reader.HasRows == false)
                {
                    lastUsedCaseId = 0;
                }
                else
                {
                    while (db.reader.Read())   //get last caseId
                        lastUsedCaseId = db.reader.GetInt32(0);
                }

                db.reader.Close();
                lastUsedCaseId++;

                db.command.CommandText = ("INSERT INTO `finalproject`.`case` (`idCase`,`Typicality`, `chunk_idChunk`, `IsIndex`, `user_idUser`) VALUES (@idCase,@Typicality, @chunk_idChunk, @IsIndex, @User_idUser);");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@idCase", lastUsedCaseId);
                db.command.Parameters.AddWithValue("@Typicality", 0);
                db.command.Parameters.AddWithValue("@chunk_idChunk", chunkid);
                db.command.Parameters.AddWithValue("@IsIndex", index);
                db.command.Parameters.AddWithValue("@User_idUser", userID);
                db.command.ExecuteNonQuery();
                db.command.Parameters.Clear();
                bool addedOk = AddAuListToCaseInDB(lastUsedCaseId, pictureID);

                return new ReplyMsg(addedOk);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }



        }

        /// <summary>
        /// Add Au`s to Cases
        /// 1. will Add all au`s of given picture to case
        /// 2. table case_has_aus will be updated
        /// 3. will be called in CreateNewCase
        /// </summary>
        /// <param name="caseID"></param>
        /// <param name="pictureId"></param>
        /// <returns></returns>
        public static bool AddAuListToCaseInDB(int caseID, int pictureId)
        {

            try
            {
                db = DBConnection.Instance;

                //Go over all Au`s of picture and add it to database
                foreach (int au in InitialPicturesDictionary[pictureId].AUList)
                {
                    db.command.CommandText = ("INSERT INTO `finalproject`.`case_has_au` (`au_idAU`, `case_IdCase`) VALUES (@au_idAU, @case_IdCase);");
                    db.command.Prepare();
                    db.command.Parameters.Clear();
                    db.command.Parameters.AddWithValue("@au_idAU", au);
                    db.command.Parameters.AddWithValue("@case_IdCase", caseID);
                    db.command.ExecuteNonQuery();
                    db.command.Parameters.Clear();
                }
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return false;
            }




        }

        /// <summary>
        /// method CreateNewChunk if chunk not exists in data base
        /// 1. will update table chunk with new entry.
        /// 2. will be called by Gui
        /// </summary>
        /// <param name="label"></param>
        /// <returns></returns>
        public static ReplyMsg CreateNewChunk(string label)
        {
            int chunkid = 0;//initial chunkid - as the last chunkid in db
            //
            try
            {
                ReplyMsg msg = CheckIfChunkExists(label);
                if ((bool)msg.MessageState == true)
                {
                    chunkid = (int)msg.ParamArray[0];
                }
                else //if chunk not exist - create new chunk
                {

                    db = DBConnection.Instance;
                    
                    db.command.CommandText = ("SELECT idChunk FROM finalproject.chunk ORDER BY idChunk");
                    
                    db.reader = db.command.ExecuteReader();
                    while (db.reader.Read());//gets last chunkid
                    chunkid = db.reader.GetInt32(0);
                    db.reader.Close();
                    chunkid++; //the new chunkid num
                    
                    // insert new chunk
                    db.command.CommandText = ("INSERT INTO `finalproject`.`chunk` (`idChunk`, `Label`) VALUES (@idChunk, @Label);");
                    db.command.Prepare();
                    db.command.Parameters.Clear();
                    db.command.Parameters.AddWithValue("@idChunk", chunkid);
                    db.command.Parameters.AddWithValue("@Label", label);
                    db.command.ExecuteNonQuery();
                    db.command.Parameters.Clear();
                }
                return new ReplyMsg(true, chunkid);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }

        }
        public static ReplyMsg CheckIfChunkExists(string label)
        {
            int chunkid = -1;
            try
            {
                db = DBConnection.Instance;
                db.reader.Close();
                db.command.CommandText = ("SELECT idChunk FROM finalproject.chunk WHERE Label = @label;");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@label", label);
                
                db.reader = db.command.ExecuteReader();

                if (db.reader.Read())//if chunk exists do nothing
                {
                    chunkid = db.reader.GetInt32(0);
                    
                    return new ReplyMsg(true, chunkid);
                }
                //if chunk not exists
                db.reader.Close();
                return new ReplyMsg(false);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }
        }
        /// <summary>
        /// this method will be called by DatabaseImage to set Au`s of picture in database
        /// 1. will update pictures_has_au
        /// </summary>
        /// <returns></returns>
        public static ReplyMsg AddAusToPictureInDB(Picture picture)
        {

            try
            {
                db = DBConnection.Instance;

             //   ICollection<Picture> picturesList = initialEndowPictures.Values;
              //  foreach (Picture pic in picturesList)
                {
                    //Go over all Au`s of picture and add it to database
                    foreach (int au in picture.AUList)
                    {
                        db.command.CommandText = ("INSERT INTO `finalproject`.`pictures_has_au` (`Pictures_picturesId`, `au_idAU`) VALUES (@Pictures_picturesId, @au_idAU);");
                        db.command.Prepare();
                        db.command.Parameters.Clear();
                        db.command.Parameters.AddWithValue("@Pictures_picturesId", picture.PictureID);
                        db.command.Parameters.AddWithValue("@au_idAU", au);
                        db.command.ExecuteNonQuery();
                    }

                }

                return new ReplyMsg(true); ;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }
        }

        public static void UpdatePicturesinDB(Picture picture)
        {

            try
            {
                db = DBConnection.Instance;

                //Go over all Au`s of picture and add it to database

                {
                    db.command.CommandText = string.Format("UPDATE pictures SET chunk_idChunk={0}, IsChecked={1} WHERE picturesId={2}", picture.ChunkID, picture.IsChecked, picture.PictureID);
                    db.command.ExecuteNonQuery();
                }

            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());

            }


        }

        public static ReplyMsg UpdateHasAu(int pictureID, bool hasAUs)
        {
            try
            {
                db = DBConnection.Instance;

                //Go over all Au`s of picture and add it to database

                
                    db.command.CommandText = string.Format("UPDATE pictures SET HasAUs={0} WHERE picturesId={1}",hasAUs, pictureID);
                    db.command.ExecuteNonQuery();
                    reviewPictuersDictionary.Add(pictureID, newAddedPictuersDictionary[pictureID]);
                    newAddedPictuersDictionary.Remove(pictureID);
                    return new ReplyMsg(true);

            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return new ReplyMsg(false);
            }

        }

        /// <summary>
        /// GetAUsofPictures - Au`s that update in Database will be uploaded by this function
        /// </summary>
        /// <param name="picturesid"></param>
        /// <returns></returns>
        public static void GetAUsOfPicturesFromDB(Dictionary<int, Picture> PicturesDictionary)
        {

            try
            {
                foreach (Picture pictures in PicturesDictionary.Values)
                {

                    db = DBConnection.Instance;
                    //create command
                    db.command.CommandText = ("SELECT pictures_has_au.au_idAU From pictures_has_au where pictures_has_au.Pictures_picturesId=@Pictures_picturesId;");
                    db.command.Prepare();
                    db.command.Parameters.Clear();
                    db.command.Parameters.AddWithValue("@Pictures_picturesId", pictures.PictureID);
                    
                    db.reader = db.command.ExecuteReader();

                    while (db.reader.Read())
                    {
                        pictures.AUList.Add(db.reader.GetInt32(0));
                    }

                    db.reader.Close();
                }
            }

            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());

            }



        }

        #endregion

        #region SetIndexCases methods
        /// <summary>
        /// method to Set Indnex Case
        /// the method will put 1- if index  in case table in cloumn "IsIndex"
        /// </summary>
        public static void SetIndexCases(bool secondRun)
        {
            db = DBConnection.Instance;

            //List of case that have only one AU   Case[AU1]
            List<Case> singleAUCaseList = new List<Case>();
            //List of case that have more than one AU  Case [AU2,AU3]
            List<Case> multipleAUCaseList = new List<Case>();
            try
            {

                db.command.CommandText = ("select case_IdCase ,COUNT(*), chunk_idChunk as countau from case_has_au,finalproject.case WHERE case_IdCase = finalproject.case.idCase Group by case_IdCase;");


                
                db.reader = db.command.ExecuteReader();


                while (db.reader.Read())
                {
                    if (db.reader.GetInt32(1) > 1)
                    {
                        //get all cases with more than one au
                        Case caseClass = new Case(db.reader.GetInt32(0), 0, db.reader.GetInt32(2), new List<int>(),false);
                        multipleAUCaseList.Add(caseClass);
                    }
                    else
                    {
                        //get all cases with single AU (standalone cases)  //singles no matter their chunk size will be index
                        Case caseClass = new Case(db.reader.GetInt32(0), 0, 0, new List<int>(),true);
                        singleAUCaseList.Add(caseClass);

                    }
                }

                db.reader.Close();

                //Get all Au`s of  singleCaseList
                GetAusByCaseID(singleAUCaseList);
                //Get all Au`s of  multipleAUCaseList
                GetAusByCaseID(multipleAUCaseList);
                //will set index=1 to cases with single au
                UpdateIndexCases(singleAUCaseList);
                //check if case is index or not
                
                CheckCaseIfIndex(multipleAUCaseList, CreateSingleAusArray(singleAUCaseList));
                if (secondRun == true)
                    CheckCaseIfIndexSecondPass(multipleAUCaseList);
                //will set index=1 to cases that are not composed from standalone au
                UpdateIndexCases(multipleAUCaseList);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());

            }


        }

        private static void CheckCaseIfIndex(List<Case> cases, List<int> singleUAUs)
        {

            /* example 
             *  Case [AU6+AU12]
                
             * 1. if  singleUAUs contain AU6 or AU12 ---> remove Case [AU6+AU12] from  cases multipleAUCaseList
             * 
             * 2. cases that not contain standalone AU`s will Remain  multipleAUCaseLis 
             * 3. case that remain in multipleAUCaseLis will be marked as Indexes
             */
            //for each multipleAUCaseList
            chunkSizes = new Dictionary<int, int>();
            ReplyMsg msg = GetChunks();
            if (msg.MessageState == true)
            {
                chunks = (List<Chunk>)msg.ParamArray[0]; //get chunks list
                foreach (Chunk chunki in chunks)//fill chunks sizes
                {
                    ReplyMsg msgChunkSize = CheckChunkSize(chunki);
                    if (msgChunkSize.MessageState == true)
                        chunkSizes.Add(chunki.ChunkId, (int)msgChunkSize.ParamArray[0]);
                }
            }
            for (int i = 0; i < cases.Count; i++)
            {
                bool removed = false;
                foreach (int au in cases[i].AuList) //for each case in multiple case list check if contain a standalone case
                {
                    if (singleUAUs.Contains(au))
                    {
                        removed = true; //if so this case wont be index
                    }

                }

                if (removed)
                {
                    if (chunkSizes[cases[i].ChunkID] > 1)// if the supposed index is not ment to be index check
                    {                                   //if his chunk is empty if so the case will be index no matter the removed flag
                        cases[i].IsIndex = false;
                    }
                }
                else
                    cases[i].IsIndex = true;
            }


        }

        private static void CheckCaseIfIndexSecondPass(List<Case> cases)
        {

            /* example 
             *  Case [AU6+AU12]
                
             * 
             * 1. if  case(1+2),(1+2+3)  (isindex ==true) -> case(1+2+3) (is index == false) 
             * 
             * 2. cases that not contain big standalones all cases AU`s will Remain  multipleAUCaseLis 
             * 3. case that remain in multipleAUCaseLis will be marked as Indexes
             */
            //for each multipleAUCaseList
            //create the sorted list
            SortedList<int, List<Case>> sortedList = new SortedList<int, List<Case>>();

            foreach (Case casesidx in cases)
            {
                List<Case> caseList; //value to save case list
                    if (sortedList.TryGetValue(casesidx.AuList.Count, out caseList) == false)//gets the case list made for case size
                    {
                        sortedList.Add(casesidx.AuList.Count, new List<Case>());//add new case list for case size
                        sortedList.TryGetValue(casesidx.AuList.Count, out caseList); //take the case list made
                        caseList.Add(casesidx);//adds the new Case to sorted list
                    }
                    else
                    {
                        caseList.Add(casesidx);//adds the new Case to sorted list
                    }
                
            }

            foreach (List<Case> caseListI in sortedList.Values)
            {
                foreach (Case caseI in caseListI)
                {
                    if (caseI.IsIndex == true) //continue to next case if this case isnt index
                    {
                        foreach (List<Case> caseListJ in sortedList.Values)// only if case j size is bigger than case i size
                        {
                            if (sortedList.IndexOfValue(caseListJ) > sortedList.IndexOfValue(caseListI)) //check cases that are bigger than caseI in AuList.count
                            {
                                foreach (Case caseJ in caseListJ)
                                {
                                    if (caseJ.IsIndex == true)//continue to next case if this case isnt index
                                    {
                                        bool isJIndex = false;
                                        foreach (int au in caseI.AuList)
                                        {
                                            if (caseJ.AuList.Contains(au) == false)
                                                isJIndex = true; //at least one time that au of caseI dont apeares in caseJ -> caseJ is index 
                                        }
                                        if (isJIndex == false)
                                        {
                                            if (chunkSizes[caseJ.ChunkID] > 1)// if the supposed index is not ment to be index check
                                            {                                        //if his chunk is empty if so the case will be index no matter the removed flag
                                                caseJ.IsIndex = false;
                                            }
                                        }
                                        //else we already desided in the first run 
                                        //so the first run decision remains
                                    }
                                }
                            }
                        }
                    }
                }

            }
            //returns the sortedCaseList to cases
            cases = new List<Case>();
            foreach(List<Case> casesList in sortedList.Values)
            {
                foreach (Case casei in casesList)
                    cases.Add(casei);
            }

        }



        private static void UpdateIndexCases(List<Case> cases)
        {
            try
            {
                db = DBConnection.Instance;

                foreach (Case caseClass in cases)
                {
                    {
                        db.command.CommandText = string.Format("UPDATE finalproject.case SET IsIndex={0} WHERE idCase={1}", caseClass.IsIndex , caseClass.CaseID);
                        db.command.ExecuteNonQuery();
                    }
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());

            }


        }

        private static List<int> CreateSingleAusArray(List<Case> cases)
        {
            List<int> AuArray = new List<int>();
            //for each singleAUCaseList - get AU and put into array
            foreach (Case caseClass in cases)
            {
                foreach (int au in caseClass.AuList)
                {

                    AuArray.Add(au);
                }


            }

            return AuArray;
        }

        #endregion

        #region Retrieval methods

        /// <summary>
        /// Get All cases that are Index Cases
        /// all cases that are index will be added to List<case> cases
        /// </summary>
        /// <param name="cases"></param>
        public static void GetIndexCases(List<Case> cases)
        {
            db = DBConnection.Instance;
            try
            {
                //create command
                //need to check queury
                db.command.CommandText = ("SELECT idCase, Typicality, chunk_idChunk, au_idAU FROM finalproject.case_has_au, finalproject.case, finalproject.user WHERE idCase = case_idCase and IsIndex = 1 and User_idUser = user.idUser and User_idUser = 2 ORDER BY idCase;");
                
                db.reader = db.command.ExecuteReader();
                //start recieving rows
                Case lastCase = null;
                while (db.reader.Read())
                {
                    //get line params
                    int idCase, typicality, idChunk, idAU;
                    idCase = db.reader.GetInt32(0);
                    typicality = db.reader.GetInt32(1);
                    idChunk = db.reader.GetInt32(2);
                    idAU = db.reader.GetInt32(3);

                    //if new case arrived do this:
                    if ((lastCase != null) && (lastCase.CaseID != idCase))
                        cases.Add(lastCase);
                    //fill case details
                    if ((lastCase == null) || (lastCase.CaseID != idCase))
                    {
                        lastCase = new Case();
                        lastCase.CaseID = idCase;
                        lastCase.Typicality = typicality;
                        lastCase.ChunkID = idChunk;
                        lastCase.AuList = new List<int>(); //new aulist for case
                    }
                    lastCase.AuList.Add(idAU);
                }
                db.reader.Close();
                if (lastCase != null)
                    cases.Add(lastCase);
                db.command.Parameters.Clear();
                
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }
        }

        /// <summary>
        ///  //get all Au`s of all cases in List<cases>
        //   fill  List <int> AUlist property in every Case
        /// </summary>
        /// <param name="cases"></param>
        public static void GetAusByCaseID(List<Case> cases)
        {

            try
            {

                foreach (Case caseClass in cases)
                {
                    db = DBConnection.Instance;
                    //create command
                    //need to check queury
                    db.command.CommandText = ("SELECT case_has_au.au_idAU From case_has_au where case_has_au.case_idCase=@caseID;");
                    db.command.Prepare();
                    db.command.Parameters.Clear();
                    db.command.Parameters.AddWithValue("@caseID", caseClass.CaseID);
                    
                    db.reader = db.command.ExecuteReader();

                    while (db.reader.Read())
                    {
                        caseClass.AuList.Add(db.reader.GetInt32(0));
                    }
                    db.reader.Close();
                    db.command.Parameters.Clear();
                    
                }
            }

            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }





        }


        /// <summary>
        ///  //Get all cases of chunk ID 
        /// fill List<Cases> with all cases of chunk

        public static void GetCasesByChunkID(int chunkID, List<Case> cases)
        {
            db = DBConnection.Instance;
            try
            {
                //create command
                //need to check queury
                db.command.CommandText = ("SELECT idCase, Typicality, chunk_idChunk, au_idAU FROM finalproject.case_has_au, finalproject.case, finalproject.user WHERE idCase = case_idCase and User_idUser = user.idUser and User_idUser = 2 and chunk_idChunk = @chunkID ORDER BY idCase;");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@chunkID", chunkID);
                
                db.reader = db.command.ExecuteReader();
                //start recieving rows
                Case lastCase = null;
                while (db.reader.Read())
                {
                    //get line params
                    int idCase, typicality, idChunk, idAU;
                    idCase = db.reader.GetInt32(0);
                    typicality = db.reader.GetInt32(1);
                    idChunk = db.reader.GetInt32(2);
                    idAU = db.reader.GetInt32(3);

                    //if new case arrived do this:
                    if ((lastCase != null) && (lastCase.CaseID != idCase))
                        cases.Add(lastCase);
                    //fill case details
                    if ((lastCase == null) || (lastCase.CaseID != idCase))
                    {
                        lastCase = new Case();
                        lastCase.CaseID = idCase;
                        lastCase.Typicality = typicality;
                        lastCase.ChunkID = idChunk;
                        lastCase.AuList = new List<int>(); //new aulist for case
                    }
                    lastCase.AuList.Add(idAU);
                }
                db.reader.Close();
                if (lastCase != null)
                    cases.Add(lastCase);
                db.command.Parameters.Clear();
                
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }

        }

        /// <summary>
        /// Get  instacne of Chunk by given chunkID
        /// </summary>
        /// <param name="chunkID"></param>
        /// <returns></returns>
        public static Chunk GetchunkByChunkID(int chunkID)
        {

            try
            {
                db = DBConnection.Instance;
                db.command.CommandText = ("SELECT * FROM finalproject.chunk where chunk.idChunk=@idChunk;");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@idChunk", chunkID);
                
                db.reader = db.command.ExecuteReader();
                Chunk chunk = null;
                while (db.reader.Read())
                {
                    chunk = new Chunk(db.reader.GetInt32(0), db.reader.GetString(1));

                }
                db.reader.Close();
                
                db.command.Parameters.Clear();
                return chunk;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return null;
            }


        }

        /// <summary>
        /// Get Last caseID from cases Table
        /// it will be used to add new case with new caseID
        /// </summary>
        /// <returns></returns>
        public static int GetLastCaseID()
        {
            int lastUsedCaseId = 0;
            try
            {
                db = DBConnection.Instance;
                db.command.CommandText = "SELECT case.idCase FROM finalproject.case ORDER BY case.idCase";
                db.reader = db.command.ExecuteReader();
                //go to the Last line
                if (db.reader.HasRows == false)
                {
                    lastUsedCaseId = 0;
                }
                else
                {
                    while (db.reader.Read()) ;
                    //get last caseid
                    lastUsedCaseId = db.reader.GetInt32(0);
                }

                db.reader.Close();
                
                return ++lastUsedCaseId;

            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return -1;
            }


        }
        /// <summary>
        /// Remove Case from DB 
        /// 1. the methd delete all rows of case in table Au_has_case
        /// 2.the method get case id and remove it from case table
        /// </summary>
        /// <param name="caseID"></param>
        /// <returns></returns>
        //not in use
        public static bool RemoveCasefroDB(int caseID)
        {

            try
            {
                db = DBConnection.Instance;
                //delete from case_has_au all cases au
                db.command.CommandText = ("Delete  From  finalproject.case_has_au where case_has_au.case_idCase=@idCase;");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@idCase", caseID);

                db.command.ExecuteNonQuery();
                db.command.Parameters.Clear();
                


                //delete case from case table
                db.command.CommandText = ("Delete From finalproject.case where case.idCase=@idCase;");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@idCase", caseID);
                db.command.ExecuteNonQuery();
                db.command.Parameters.Clear();
                
                return true;
            }


            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return false;
            }



        }

        public static ReplyMsg GetLastPictureID()
        {
            int lastUsedCaseId = 0;
            try
            {
                db = DBConnection.Instance;
                db.command.CommandText = "SELECT pictures.picturesId FROM finalproject.pictures ORDER BY pictures.picturesId";
                db.reader = db.command.ExecuteReader();
                //go to the Last line
                if (db.reader.HasRows == false)
                {
                    lastUsedCaseId = 0;
                }
                else
                {
                    while (db.reader.Read()) ;
                    //get last caseid
                    lastUsedCaseId = db.reader.GetInt32(0);
                }

                db.reader.Close();

                return new  ReplyMsg(true, lastUsedCaseId);

            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }

        }

        public static ReplyMsg CreateNewPicutreInDB(string fileName)
        {
            int lastpictureId = 0;
            try
            {

                db = DBConnection.Instance;
                db.command.CommandText = ("SELECT pictures.picturesId FROM finalproject.pictures ORDER BY pictures.picturesId");

                
                db.reader = db.command.ExecuteReader();
                //go to the Last line
                if (db.reader.HasRows == false)
                {
                    lastpictureId = 0;
                }
                else
                {
                    while (db.reader.Read()) ;
                    //get last caseid
                    lastpictureId = db.reader.GetInt32(0);
                }
                db.reader.Close();

                db.command.CommandText = ("INSERT INTO `finalproject`.`pictures` (`picturesId`,`Path`, `chunk_idChunk`, `IsChecked`, `ForReview`,`HasAUs`) VALUES (@picturesId,@Path, @chunk_idChunk, @IsChecked, @ForReview,@HasAUs);");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@picturesId", ++lastpictureId);
                db.command.Parameters.AddWithValue("@Path", fileName);
                db.command.Parameters.AddWithValue("@chunk_idChunk", 0);
                db.command.Parameters.AddWithValue("@IsChecked", 0);
                db.command.Parameters.AddWithValue("@ForReview", 1);
                db.command.Parameters.AddWithValue("@HasAUs", 1);
                db.command.ExecuteNonQuery();
                db.command.Parameters.Clear();
                return new ReplyMsg(true);

            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }

        }
        public static ReplyMsg CreateNewPicutreInDB(string fileName, int sysVar)
        {
            int lastpictureId = 0;
            try
            {

                db = DBConnection.Instance;
                db.command.CommandText = ("SELECT pictures.picturesId FROM finalproject.pictures ORDER BY pictures.picturesId");
                
                db.reader = db.command.ExecuteReader();
                //go to the Last line
                if (db.reader.HasRows == false)
                {
                    lastpictureId = 0;
                }
                else
                {
                    while (db.reader.Read())
                        lastpictureId = db.reader.GetInt32(0);
                }
                db.reader.Close();
                ++lastpictureId;
                db.command.CommandText = ("INSERT INTO `finalproject`.`pictures` (`picturesId`,`Path`, `chunk_idChunk`, `IsChecked`, `ForReview`,`HasAUs`, `Folder`) VALUES (@picturesId,@Path, @chunk_idChunk, @IsChecked, @ForReview,@HasAUs,@Folder);");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@picturesId", lastpictureId);
                db.command.Parameters.AddWithValue("@Path", fileName);
                db.command.Parameters.AddWithValue("@chunk_idChunk", 0);
                db.command.Parameters.AddWithValue("@IsChecked", 0);
                db.command.Parameters.AddWithValue("@ForReview", 1);
                db.command.Parameters.AddWithValue("@HasAUs", 0);
                db.command.Parameters.AddWithValue("@Folder", sysVar);

                db.command.ExecuteNonQuery();

                db.command.Parameters.Clear();
                Picture newPic = new Picture(lastpictureId, pathMatcher[sysVar] + fileName, 0, 0);
                newAddedPictuersDictionary.Add(lastpictureId, newPic);
                return new ReplyMsg(true);

            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }

        }
        #endregion

        #region DatabaseImages methods

        public static ReplyMsg GetAllAus()
        {
          try
          {
              List<int> aulist = new List<int>();
                db = DBConnection.Instance;
                db.command.CommandText = ("SELECT * FROM au order by idAU");
                
                db.reader = db.command.ExecuteReader();

                while (db.reader.Read())
                {
                    
                    aulist.Add(db.reader.GetInt32(0));
                }

                db.reader.Close();
                return new ReplyMsg(true, aulist);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }



        }

        #endregion

        #region Adaptaion 

        public static ReplyMsg IfCaseExistInDB(int caseID)
        {
            
            try
            {
                db = DBConnection.Instance;
                db.reader.Close();
                db.command.CommandText = ("SELECT idcase FROM finalproject.case WHERE idcase = @idcase;");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@idcase", caseID);

                db.reader = db.command.ExecuteReader();
                //if no rows -- new case is shown
                if (db.reader.HasRows==false)
                {
                    db.reader.Close();
                    return new ReplyMsg(true,false);
                }
                //if case exist
                db.reader.Close();
                return new ReplyMsg(true,true);
                  
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }

        }

        public static void UpadteExisitingCaseWithChunkInDB(int caseID ,int typicallity,bool index ,int chunkID)
        {
            try
            {
                db = DBConnection.Instance;

                db.command.CommandText = string.Format("UPDATE finalproject.case SET Typicality={0},chunk_idChunk={1},IsIndex={3} WHERE idCase={2}", typicallity, chunkID, caseID, index);
                        db.command.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());

            }



        }


        public static ReplyMsg CheckIfExist(string userFeedBack)
        {
            try
            {
                db = DBConnection.Instance;
                db.command.CommandText = ("SELECT idChunk FROM chunk WHERE chunk.Label = @label;");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@Label", userFeedBack);
                
                db.reader = db.command.ExecuteReader();

                if (db.reader.Read())
                {
                    //Get userid from fisrt Column , get role from Second column
                    ReplyMsg reply = new ReplyMsg(true, db.reader.GetInt32(0));
                    //Need to close reader before Exit the method
                    db.reader.Close();
                        
                    return reply;

                }
                db.reader.Close();
                return new ReplyMsg(false);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }

        }
        public static ReplyMsg CheckChunkSize(Chunk chunkFeedback)
        {
            try
            {
                db = DBConnection.Instance;
                db.reader.Close();
                db.command.CommandText = ("SELECT COUNT(idChunk) FROM finalproject.chunk ,finalproject.case WHERE finalproject.case.chunk_idChunk = finalproject.chunk.idChunk and finalproject.chunk.idChunk = @chunkId ;");
                db.command.Prepare();
                db.command.Parameters.Clear();
                db.command.Parameters.AddWithValue("@chunkId", chunkFeedback.ChunkId);
                
                db.reader = db.command.ExecuteReader();

                if (db.reader.Read())
                {
                    int count = db.reader.GetInt32(0);
                    db.reader.Close();
                    return new ReplyMsg(true, count);
                }

                db.reader.Close();
                return new ReplyMsg(false);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }
        }
        #endregion

      #region Refinement

      public static void GetAllCases(List<Case> cases)
        {
            db = DBConnection.Instance;
            try
            {
                //create command
                //need to check queury
                db.command.CommandText = ("SELECT idCase, Typicality, chunk_idChunk, au_idAU FROM finalproject.case_has_au, finalproject.case, finalproject.user WHERE idCase = case_idCase  and User_idUser = user.idUser and User_idUser = 2 ORDER BY idCase;");

                db.reader = db.command.ExecuteReader();
                //start recieving rows
                Case lastCase = null;
                while (db.reader.Read())
                {
                    //get line params
                    int idCase, typicality, idChunk, idAU;
                    idCase = db.reader.GetInt32(0);
                    typicality = db.reader.GetInt32(1);
                    idChunk = db.reader.GetInt32(2);
                    idAU = db.reader.GetInt32(3);

                    //if new case arrived do this:
                    if ((lastCase != null) && (lastCase.CaseID != idCase))
                        cases.Add(lastCase);
                    //fill case details
                    if ((lastCase == null) || (lastCase.CaseID != idCase))
                    {
                        lastCase = new Case();
                        lastCase.CaseID = idCase;
                        lastCase.Typicality = typicality;
                        lastCase.ChunkID = idChunk;
                        lastCase.AuList = new List<int>(); //new aulist for case
                    }
                    lastCase.AuList.Add(idAU);
                }
                db.reader.Close();
                if (lastCase != null)
                    cases.Add(lastCase);
                db.command.Parameters.Clear();

            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }


        }


      #endregion


        public static void CreatePathMatcher()
        {
            DBQueries.pathMatcher.Add(0, databasePictures);
            DBQueries.pathMatcher.Add(1, externalPictures);
        }


        /// <summary>
        /// will use for Developers - to Set Database with pictures
        /// </summary>
        public static void CreatePicturesInDB()
        {
            //Get All Directories from databasePictures path
            string[] dirctorries = Directory.GetDirectories(databasePictures);

            //for each Directory get files inside 
            foreach (string str in dirctorries)
            {
              string [] files=  Directory.GetFiles(str);
              
                int num=databasePictures.Count();

              string newpath= files[0].Remove(0,num);

              DBQueries.CreateNewPicutreInDB(newpath,0);
            }
        }


        public static ReplyMsg getExternalImageName()
        {
            String picNum = "E00000";
            try
            {

                db = DBConnection.Instance;
                db.command.CommandText = ("SELECT picturesId,Path FROM finalproject.pictures WHERE Folder=1 ORDER BY picturesId;");
                db.reader = db.command.ExecuteReader();
                //go to the Last line
                while (db.reader.Read())
                {
                    picNum = db.reader.GetString(1);
                }
                    int num = Convert.ToInt32(picNum.Substring(1,5));
                    num++;
                    picNum = "E" + num.ToString().PadLeft(5,'0');
                
                db.reader.Close();
                return new ReplyMsg(true, picNum);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }

        }
        //check if case exists by aus if so return id if not false return (msgstate, exists:bool, id if exists true)
        //dynamic 

        public static ReplyMsg IfCaseExistInDBbyAUs(Case caseAdapt)
        {
            db = DBConnection.Instance;
            try
            {
                int i = 0;
                //create command
                //need to check queury
                db.command.CommandText = string.Format("SELECT case_idCase FROM finalproject.case_has_au WHERE au_idAU = @au{0};", i);
                for (i = 1; i < caseAdapt.AuList.Count; i++) //add more aus for the query and get their intersect case id 
                {
                        db.command.CommandText = db.command.CommandText.Insert(db.command.CommandText.Length - i, string.Format(" and case_idCase IN (SELECT case_idCase FROM finalproject.case_has_au WHERE au_idAU = @au{0})", i));
                }
                db.command.Prepare();
                db.command.Parameters.Clear();
                i = 0;
                foreach (int au in caseAdapt.AuList)
                {
                    db.command.Parameters.AddWithValue(string.Format("@au{0}", i), au);
                    i++;
                }
                db.reader = db.command.ExecuteReader();
                //start recieving rows
                Dictionary<int, int> idCase = new Dictionary<int, int>();
                while (db.reader.Read())
                {
                    //get line params
                    idCase.Add(db.reader.GetInt32(0), 0);
                }
                db.reader.Close();
                if(idCase.Count() == 0)
                    return new ReplyMsg(true, false);
                int[] keys = idCase.Keys.ToArray();
                foreach (int caseId in keys)
                {
                    db.command.CommandText = "SELECT Count(case_idCase) FROM finalproject.case_has_au WHERE case_idCase = @caseId;";
                    db.command.Prepare();
                    db.command.Parameters.Clear();
                    db.command.Parameters.AddWithValue("@caseId", caseId);
                    db.reader = db.command.ExecuteReader();
                    if(db.reader.Read())
                    {
                        //get line params
                        idCase[caseId] = db.reader.GetInt32(0);
                    }

                    db.reader.Close();
                }
                db.reader.Close();
                foreach (int caseId in idCase.Keys)
                {
                    int caseSize;
                    idCase.TryGetValue(caseId, out caseSize);
                    if (caseSize == caseAdapt.AuList.Count())
                    {
                        return new ReplyMsg(true, true, caseId);
                    }
                }
                return new ReplyMsg(true, false);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }
        }
        public static ReplyMsg CasesCount()
        {
            try
            {
                int count;
                db = DBConnection.Instance;
                db.command.CommandText = ("SELECT COUNT(idCase) FROM finalproject.case A WHERE (SELECT DISTINCT B.idCase FROM finalproject.case B , case_has_au WHERE case_idCase = idCase AND B.idCase = A.idCase order by idCase);");
                db.reader = db.command.ExecuteReader();
                //go to the Last line
                if (db.reader.Read())
                {
                    count = db.reader.GetInt32(0);
                    db.reader.Close();
                    return new ReplyMsg(true, count);
                }
                db.reader.Close();
                return new ReplyMsg(false);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }

        }


        

        public static ReplyMsg DataSetOfUserCases()
        {
            DataSet ds = new DataSet();
            db = DBConnection.Instance;
            try
            {
                db.command.CommandText = "SELECT idCase,chunk_idChunk,au_idAU,IsIndex FROM finalproject.case, case_has_au WHERE case_idCase = idCase ORDER BY idCase;";
                db.UpdateAdapter();
                db.adapter.Fill(ds, "Cases");
            
                return new ReplyMsg(true,ds);
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Error: " + ex.ToString());
                return new ReplyMsg(false);
            }
        }



        public static ReplyMsg chunksCount()
        {
            try
            {
               // int count;
                db = DBConnection.Instance;
                db.command.CommandText = ("SELECT idChunk, count(idCase) FROM chunk, finalproject.case where (select B.case_idCase from case_has_au B where B.case_idCase = idCase GROUP by B.case_idCase) and chunk_idChunk = idchunk group by idchunk;");
                db.reader = db.command.ExecuteReader();
                //go to the Last line
                Dictionary<int,int> chunkCount = new Dictionary<int,int>();
                while (db.reader.Read())
                {
                    chunkCount.Add(db.reader.GetInt32(0),db.reader.GetInt32(1));
                }
                db.reader.Close();
                return new ReplyMsg(true,chunkCount);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                db.reader.Close();
                return new ReplyMsg(false);
            }

        }
		public static void RefinementResultUpdate(int[] indexCaseIdDictOutput, int[] caseIdxChunkValArrOutput)
        {
            try
            {
                for (int index = 0; index < caseIdxChunkValArrOutput.Length; index++)
                {
                    if (caseIdxChunkValArrOutput[index] == -1) //delete case - case is boundary object
                    {
                        if (RemoveCasefroDB(indexCaseIdDictOutput[index]) == false)
                            throw new Exception();
                    }
                    else
                        if (UpdateCaseidChunkId(indexCaseIdDictOutput[index], caseIdxChunkValArrOutput[index]) == false)
                            throw new Exception();
                }

            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }
        }

        private static bool UpdateCaseidChunkId(int caseId, int chunkId)
        {
            try
            {
                db = DBConnection.Instance;

                //Go over all Au`s of picture and add it to database


                db.command.CommandText = string.Format("UPDATE finalproject.case SET chunk_idChunk={0} WHERE idCase={1}", chunkId, caseId);
                db.command.ExecuteNonQuery();
                return true;

            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return false;
            }

        }
    }
}
//question for the retrieval of all external pictures with no aus in them
//SELECT picturesId , Path
//FROM finalproject.pictures, finalproject.pictures_has_au
//WHERE picturesId NOT IN (SELECT Pictures_picturesId 
//FROM pictures_has_au) and ForReview = 1
//GROUP BY picturesId;